<?php
class ModelRestaurantBooking extends Model {
	public function editBooking($booking_id, $data) {
		$sql  = "UPDATE " . DB_PREFIX . "booking";
		$sql .= " SET email = '" . $this->db->escape($data['email']) . "',";
		$sql .= " telephone = '" . $this->db->escape($data['telephone']) . "',";
		$sql .= " number_of_people = '" . (int)$data['number_of_people'] . "',";
		$sql .= " date_booked = STR_TO_DATE('" . $this->db->escape($data['date_booked']) . "','%d/%m/%Y %H:%i'),";
		$sql .= " comment = '" . $this->db->escape(strip_tags($data['comment'])) . "',";
		$sql .= " booking_status_id = '" . (int)$data['booking_status_id'] . "',";
		$sql .= " user_modified = '" . (int)$this->user->getId() . "',";
		$sql .= " date_modified = NOW()";
		$sql .= " WHERE booking_id = '" . (int)$booking_id . "'";
		
		$this->db->query($sql);
	}
	
	public function deleteBooking($booking_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "booking WHERE booking_id = '" . (int)$booking_id . "'");
	}
	
	public function getBooking($booking_id) {
		$sql  = "SELECT DISTINCT *, DATE_FORMAT(date_booked, '%d/%m/%Y %H:%i') AS date_booked,";
		$sql .= " (SELECT rd.name FROM " . DB_PREFIX . "restaurant_description rd WHERE rd.restaurant_id = rb.restaurant_id AND rd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS restaurant,";
		$sql .= " (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM " . DB_PREFIX . "customer c WHERE rb.customer_id = c.customer_id) AS customer_name";
		$sql .= " FROM " . DB_PREFIX . "booking rb";
		$sql .= " WHERE rb.booking_id = '" . (int)$booking_id . "'";
		$sql .= "";
		
		$query = $this->db->query($sql);
		
		return $query->row;
	}

	public function getBookings($data = array()) {
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		
		$sql  = "SELECT rb.*, rd.name as restaurant_name, c.firstname, c.lastname";
		$sql .= " FROM " . DB_PREFIX . "booking rb";
		$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON (rb.restaurant_id = rd.restaurant_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "customer c ON (rb.customer_id = c.customer_id)";
		$sql .= " WHERE rd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

		$sql .= " AND rb.restaurant_id = '" . (int)$restaurant_id . "'";
		
		if(isset($data['filter_booking_id'])){
			$sql .= " AND rb.booking_id = '" . (int)$data['filter_booking_id'] . "'";
		}
		
		if(isset($data['filter_customer'])){
			$sql .= " AND LCASE(CONCAT(c.firstname, ' ', c.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}
	
		if(isset($data['filter_email'])){
			$sql .= " AND LCASE(rb.email) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_email'])) . "%'";
		}
	
		if(isset($data['filter_telephone'])){
			$sql .= " AND LCASE(rb.telephone) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_telephone'])) . "%'";
		}
		
		if(isset($data['filter_booking_status_id'])){
			$sql .= " AND rb.booking_status_id = '" . (int)$data['filter_booking_status_id'] . "'";
		}
		
		if(isset($data['filter_date_booked'])){
			$sql .= " AND DATE_FORMAT(rb.date_booked, '%d/%m/%Y') = '" . $data['filter_date_booked'] . "'";
		}
		
		if(isset($data['filter_date_added'])){
			$sql .= " AND DATE_FORMAT(rb.date_added, '%d/%m/%Y') = '" . $data['filter_date_added'] . "'";
		}
		
		$sort_data = array(
			'rd.name',
			'rb.booking_id',
			'rb.customer_id',
			'rb.email',
			'rb.booking_status_id',
			'rb.date_booked',
			'rb.date_added'
		);
			
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY rb.booking_status_id";	
		}
			
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
			
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}																																							  
																																							  
		$query = $this->db->query($sql);																																				
		
		return $query->rows;	
	}
	
	public function getTotalBookings($data) {
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		
		$sql  = "SELECT COUNT(*) AS total";
		$sql .= " FROM " . DB_PREFIX . "booking rb";
		$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON (rb.restaurant_id = rd.restaurant_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "customer c ON (rb.customer_id = c.customer_id)";
		$sql .= " WHERE rd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
	
		$sql .= " AND rb.restaurant_id = '" . (int)$restaurant_id . "'";
		
		if(isset($data['filter_booking_id'])){
			$sql .= " AND rb.booking_id = '" . (int)$data['filter_booking_id'] . "'";
		}
		
		if(isset($data['filter_customer'])){
			$sql .= " AND LCASE(CONCAT(c.firstname, ' ', c.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}
	
		if(isset($data['filter_email'])){
			$sql .= " AND LCASE(rb.email) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_email'])) . "%'";
		}
	
		if(isset($data['filter_telephone'])){
			$sql .= " AND LCASE(rb.telephone) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_telephone'])) . "%'";
		}
		
		if(isset($data['filter_booking_status_id'])){
			$sql .= " AND rb.booking_status_id = '" . (int)$data['filter_booking_status_id'] . "'";
		}
		
		if(isset($data['filter_date_booked'])){
			$sql .= " AND DATE_FORMAT(rb.date_booked, '%d/%m/%Y') = '" . $data['filter_date_booked'] . "'";
		}
		
		if(isset($data['filter_date_added'])){
			$sql .= " AND DATE_FORMAT(rb.date_added, '%d/%m/%Y') = '" . $data['filter_date_added'] . "'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
}
?>